package cn.edu.klmy.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import web.util.DaoUtil;
import cn.edu.klmy.dao.IStudentDao;
import cn.edu.klmy.models.student.Student;

@Repository
public class StudentDao extends JdbcDaoSupport implements IStudentDao {

    private static final String ADD_STUDENT = "INSERT INTO STUDENT(STUDENT_NAME, STUDENT_ID_NO, STUDENT_NO, STUDENT_SEX, STUDENT_RACE, STUDENT_BIRTH, STUDY_CLASS_ID, ADMIN_CLASS_ID) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

    @Override
    public void addStudent(Student student) {
        getJdbcTemplate().update(ADD_STUDENT, new Object[]{
                student.getName(),
                student.getIdentityCardNumber(),
                student.getStudentNumber(),
                student.getSex(),
                student.getNation(),
                student.getBirthday(),
                student.getTeachingClass(),
                student.getExecutiveClass()
        });
    }

    private static final String REMOVE_STUDENT = "DELETE FROM STUDENT WHERE ID = ?";

    @Override
    public void removeStudent(int studentId) {
        getJdbcTemplate().update(REMOVE_STUDENT, new Object[]{studentId});
    }

    private static final String UPDATE_STUDENT = "UPDATE STUDENT SET STUDENT_NAME = ?, STUDENT_ID_NO = ?, STUDENT_NO = ?, STUDENT_SEX = ?, STUDENT_RACE = ?, STUDENT_BIRTH = ?, STUDY_CLASS_ID = ?, ADMIN_CLASS_ID = ? WHERE ID = ?";

    @Override
    public void updateStudent(Student student) {
        getJdbcTemplate().update(UPDATE_STUDENT, new Object[]{
                student.getName(),
                student.getIdentityCardNumber(),
                student.getStudentNumber(),
                student.getSex(),
                student.getNation(),
                student.getBirthday(),
                student.getTeachingClass(),
                student.getExecutiveClass(),
                student.getId()
        });
    }

    public static final String GET_STUDENT = "SELECT * FROM STUDENT WHERE ";

    @Override
    public Student getStudent(String fieldName, Object value) {
        if (fieldName == null)
            return null;
        String sql = GET_STUDENT + fieldName + "=" + value;
        List<Student> students = (List<Student>) getJdbcTemplate().query(sql, new StudentRowMapper());
        if (null == students || students.isEmpty())
            return null;
        return students.get(0);
    }

    private static final String STUDENT_EXISTS = "SELECT COUNT(*) FROM STUDENT WHERE ID = ?";

    @Override
    public boolean studentExists(int studentId) {
        int result = getJdbcTemplate().queryForInt(STUDENT_EXISTS, new Object[]{studentId});

        if (result < 1) {
            return false;
        }
        return true;
    }

    private static final String STUDENT_EXISTS_BY = "SELECT COUNT(*) FROM STUDENT WHERE STUDENT_ID_NO = ?";

    @Override
    public boolean studentExistsByIdentityCardNumber(String identityCardNumber) {
        int result = getJdbcTemplate().queryForInt(STUDENT_EXISTS_BY, new Object[]{identityCardNumber});

        if (result < 1) {
            return false;
        }
        return true;
    }

    @Override
    public List<Student> getStudents(Map<String, Object[]> filter, int pageNum, int pageSize) {
        String sql = DaoUtil.getFuzzyQueryPageSql("STUDENT", filter, pageNum, pageSize);
        return (List<Student>) getJdbcTemplate().query(sql, new StudentRowMapper());
    }

    @Override
    public int getStudentCount(Map<String, Object[]> filter) {
        String sql = DaoUtil.getFuzzyQueryCountSql("STUDENT", filter);
        return getJdbcTemplate().queryForInt(sql);
    }

    @Resource(name = "dataSource")
    public void setSuperDataSource(DataSource dataSource) {
        super.setDataSource(dataSource);
    }

    private static class StudentRowMapper implements RowMapper<Student> {

        @Override
        public Student mapRow(ResultSet resultSet, int i) throws SQLException {
            Student student = new Student();
            student.setId(resultSet.getInt("ID"));
            student.setBirthday(resultSet.getString("STUDENT_BIRTH"));
            student.setName(resultSet.getString("STUDENT_NAME"));
            student.setNation(resultSet.getInt("STUDENT_RACE"));
            student.setSex(resultSet.getInt("STUDENT_SEX"));
            student.setIdentityCardNumber(resultSet.getString("STUDENT_ID_NO"));
            student.setStudentNumber(resultSet.getString("STUDENT_NO"));
            student.setExecutiveClass(resultSet.getInt("ADMIN_CLASS_ID"));
            student.setTeachingClass(resultSet.getInt("STUDY_CLASS_ID"));
            return student;
        }
    }
}
